package com.situ.personal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.personal.entity.Department;
import com.situ.personal.entity.Role;
import com.situ.personal.util.JDBCUtil;

public class RoleDao {
	
	public List<Role> findAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Role> roleList = new ArrayList<Role>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,sn from role";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String sn =  resultSet.getString("sn");
				Role role = new Role(id, name, sn);
				roleList.add(role);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return roleList;
	}

	public int getTotalPage() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count= 0;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select count(*) from role";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("RoleDao/getTotalPage 49行："+preparedStatement); //测试   打印SQL语句
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next() ) {
				count = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		System.out.println("count："+count);
		return count;
	}

	public List<Role> findByPage(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Role> list = new ArrayList();
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,sn from role limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println("RoleDao/findByPage 65行："+preparedStatement); //测试   打印SQL语句
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next() ) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String sn= resultSet.getString("sn");
				Role role = new Role(id, name, sn);
				System.out.println(role);
				list.add(role);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		System.out.println(list);
		return list;
	}

	public boolean deleteById(int id) {
		boolean isDelete =false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from role where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println("RoleDao/deleteById/105Lines"+preparedStatement);
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isDelete = true;
				System.out.println("RoleDao_SQL删除成功");
			}else {
				System.out.println("RoleDao_SQL删除失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(null, preparedStatement, connection);
		}
		return isDelete;
	}

	public boolean insertRole(String roleName, String roleSn) {
		boolean isInsert = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO role(`name`,`sn`) VALUES(?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, roleName);
			preparedStatement.setString(2, roleSn);
			System.out.println("DepartmentDao/insertDepartment/129Lines"+preparedStatement); //SQL打印测试
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isInsert = true;
				System.out.println("DepartmentDao_SQL添加成功");
			}else {
				System.out.println("DepartmentDao_SQL添加失败");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(null, preparedStatement, connection);
		}

		return isInsert;
	}

	public Role selectById(int roleId) {
		Role role = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;	
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select * from role where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1,roleId);
			System.out.println("RoleDao/selectById/159Lines"+preparedStatement); //SQL 打印测试
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String sn = resultSet.getString("sn");
				role = new Role(id, name, sn);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return role;
	}

	public boolean updateRoleInfo(int id, String name, String sn) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean isSuccess = false;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update role set name=?,sn=? where id=?";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println("更新功能SQL/UserDao184Line："+preparedStatement);
			preparedStatement.setString(1,name);
			preparedStatement.setString(2,sn);
			preparedStatement.setInt(3,id);
			System.out.println("DepartmentDao/updateDepartmentInfo/187Lines"+preparedStatement); //SQL 打印测试
			int count = preparedStatement.executeUpdate();
			if (count>0) {
				isSuccess = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(resultSet, preparedStatement, connection);
		}
		return isSuccess;
	}
}
